package tw.com.softleader.reins.demo.car.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import tw.com.softleader.reins.demo.car.entity.Car;

@Repository
public class CarDAOjdbc implements CarDAO{
	@Autowired
	private DataSource dataSource;
	
	//新增
	static private final String INSERT_STMT = "INSERT INTO car(seats,power,brand,color) VALUES(?,?,?,?)";
	@Override
	public Integer insert(Car car) {
		Connection conn=null;
		PreparedStatement stmt=null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(INSERT_STMT);
			stmt.setInt(1, car.getSeats());
			stmt.setDouble(2, car.getPower());
			stmt.setString(3, car.getBrand());
			stmt.setString(4, car.getColor());
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	//修改
	static private final String UPDATE_STMT = "UPDATE car SET seats=?,power=?,brand=?,color=? Where id=?";
	@Override
	public Integer update(Car car) {
		Connection conn=null;
		PreparedStatement stmt=null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(UPDATE_STMT);
			stmt.setInt(1, car.getSeats());
			stmt.setDouble(2, car.getPower());
			stmt.setString(3, car.getBrand());
			stmt.setString(4, car.getColor());
			stmt.setInt(5, car.getId());
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	//刪除
	static private final String DELETE_STMT = "DELETE FROM car WHERE id=?";
	@Override
	public Integer delete(Integer id) {
		Connection conn=null;
		PreparedStatement stmt=null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(DELETE_STMT);
			stmt.setInt(1, id);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	//搜尋全部
	static private final String SELECT_ALL = "SELECT * FROM car ORDER BY id";
	@Override
	public List<Car> selectAll() {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		List<Car> cars = new ArrayList<Car>();
		Car car = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(SELECT_ALL);
			rs = stmt.executeQuery();
			while(rs.next()){
				car = new Car();
				car.setId(rs.getInt("id"));
				car.setSeats(rs.getInt("seats"));
				car.setPower(rs.getDouble("power"));
				car.setBrand(rs.getString("brand"));
				car.setColor(rs.getString("color"));
				cars.add(car);
			}
			return cars;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt!=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	//用ID搜尋
	static private final String SELECT_BY_ID = "SELECT * FROM car WHERE id=?";
	@Override
	public Car selectById(Integer id) {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_ID);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			rs.next();
			Car car = new Car();
			car.setId(rs.getInt("id"));
			car.setSeats(rs.getInt("seats"));
			car.setPower(rs.getDouble("power"));
			car.setBrand(rs.getString("brand"));
			car.setColor(rs.getString("color"));
			return car;
		} catch (SQLException e) {
			new IllegalArgumentException("查無此筆資料");
			return null;
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}
	
}
